if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_saveLeave')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_saveLeave
GO
create PROCEDURE [dbo].[bd_proc_saveLeave]
@startDate AS DATE,
@endDate AS DATE,
@description AS VARCHAR ( 2000 ),
@totalDuration AS DECIMAL,
@orderNo AS VARCHAR ( 10 ),
@leaveId AS VARCHAR ( 20 ),
@glyNo AS VARCHAR ( 20 ),
@orderId as NUMERIC,
@ishaveedu as numeric,--是否是额度假，0：不是，1：是
@applyType as numeric--1:个人界面申请，2：批量界面申请
AS
BEGIN
	DECLARE @user_serial NUMERIC
	DECLARE @dept_serial numeric --本年福利固定不变
	DECLARE @dept_name VARCHAR(100) --临时数据
	DECLARE @count INT
	DECLARE @totalCount int
	--假类信息
	--DECLARE @ishaveedu int --是否是额度假，并且0或null表示不是额度假，1表示调休，2表示年假
	DECLARE @usable_time numeric --可用时间量（分钟）
	DECLARE @lastnj numeric --上年年假
	DECLARE @lastfl numeric --上年福利
	DECLARE @nj numeric --本年年假
	DECLARE @fl numeric --本年福利
	DECLARE @old_lastnj numeric --上年年假固定不变
	DECLARE @old_lastfl numeric --上年福利固定不变
	DECLARE @old_nj numeric --本年年假固定不变
	DECLARE @old_fl numeric --本年福利固定不变
	DECLARE @temp_time numeric --临时数据
	DECLARE @status numeric --单子审批状态
	DECLARE @njcount numeric --年假总数（非剩余总数）
	DECLARE @txcount numeric --调休总数（非剩余总数）

	DECLARE @ho_year int
	DECLARE @ho_month int

	set @ho_year = YEAR(GETDATE())
	set @ho_month = MONTH(GETDATE())

	begin try
		begin tran
			--创建 返回不满足请假的人的原因
			create table #return_table(
				user_serial numeric not null ,
				user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS not NULL
			)
			--创建可提交的人员列表
			select * into #qualified_people from bd_temp_checkPeople where 1=2;
			--定义游标
			--声明一个游标userOfLeave
			DECLARE userOfLeave CURSOR LOCAL FOR SELECT
				b.user_serial,
				u.user_dep as dept_serial,
				b.dept_name
			FROM
				bd_temp_checkPeople b
				INNER JOIN dt_user u on u.user_serial = b.user_serial
			WHERE order_id = @orderId

			OPEN userOfLeave FETCH NEXT FROM userOfLeave INTO @user_serial,@dept_serial,@dept_name--提取第一条数据存放在变量中
			WHILE @@fetch_status = 0
				BEGIN
				--重置@totalCount
					set @totalCount = 0;--处理请假日期中包含在封账日期内，过滤掉，提示：部分请假时间包含在封账时段内，这个和另外（下面的）两种不符合条件独立出来，这里不符合，就不去验证其他。


					select @count = count(1) from bd_kq_accounts a
					INNER JOIN (
					SELECT
						@user_serial as userSerial,*
					FROM
						( SELECT YEAR ( CONVERT ( DATE, leave_date )) AS leaveyear, MONTH ( CONVERT ( DATE, leave_date )) AS leavemonth FROM bd_temp_dayDetail where order_id = @orderId ) b
					GROUP BY
						b.leaveyear,
						b.leavemonth) c on a.user_serial = c.userSerial and a.year = c. leaveyear and a.month = c.leavemonth
				--更新@totalCount
					set @totalCount = @count;
				if (@count>0)
					BEGIN
						--请假的日期中有已封账的日期
						insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
								SELECT
									u.user_serial,
									u.user_no,
									u.user_lname,
									d.dep_name,
									'部分请假时间包含在封账时段内'
								FROM
									dt_user u
									INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
									where u.user_serial = @user_serial
					END
					--不存在封账的日期
					ELSE BEGIN
						--1、请假人的相同请假日期是否存在相同的假类（和已请过的假单明细对比）
						select
							@count = count(1)
						from bd_temp_checkPeople p
						INNER JOIN lr_kqingj l on l.user_serial = p.user_serial
						INNER JOIN bd_temp_dayDetail d on d.leave_date = CONVERT(varchar(10),l.kssj,120)
						INNER JOIN bd_leave_order o on o.order_id = l.order_id
						where p.user_serial = @user_serial and p.order_id = @orderId and d.order_id = @orderId
						and l.bh = @leaveId
						and o.status!=2
						and o.is_del!=-1
						and l.whether_validity!=2;
						--更新@totalCount
						set @totalCount = @count;
						--@count>0表示本次请假日期，和已请假有冲突
						if(@count>0)
							BEGIN
								insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
								SELECT
									u.user_serial,
									u.user_no,
									u.user_lname,
									d.dep_name,
									'请假类型冲突(同天不能同假)'
								FROM
									dt_user u
									INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
									where u.user_serial = @user_serial
							END
						--2、请假人假类额度是否充足（本次请假的天数*每天请假的时长要小于可用额度）
						--判断该假有额度功能,这里先设固定值，之后修改为查询获得
						--select @ishaveedu = isnull(ishaveedu,0) from kt_qingj where bh = @leaveId;
						--
						if @ishaveedu!=0
						BEGIN
							if @ishaveedu=1
							BEGIN
								select @usable_time =txts,@txcount = txcount from bd_nianjia where user_serial = @user_serial;
							END
							ELSE IF @ishaveedu=2
							BEGIN
							--逻辑变更：指定日期3月1日之后不能使用上年年假（上年福利、上年法定），需要两个日期限制（申请日期、和请假日期）
							--申请日期在3月1日之后的全部申请都不考虑上年年假，
							--申请日期在3月1日之前的，需要考虑请假日期是不是有3月1日之前的，若有，则可以使用上年年假,反之，不能
							--所以，计算假类可用需要分别统计3月1日前后的使用时间，时间堆积到最后，若可用时间不足，当年年假剩余必为负数。
							--可以以此来判断年假额度是否足够，（不足则移除人员，足，则本次计算的各个账户剩余量即可作为已扣除之后的余额来使用）
							--3月1日之前的
								select @lastnj = lastfdnj,
												@lastfl=lastflnj,
												@nj =fdnjts,
												@fl = flnjts,
												@njcount = njcount
								from bd_nianjia where user_serial = @user_serial;
								set	@old_lastnj = @lastnj
								set @old_lastfl = @lastfl
								set @old_nj = @nj
								set @old_fl = @fl

								--3月1日前申请
								if(convert(varchar(10),GETDATE(),120)<'2020-03-01')
									BEGIN
										--包含3月1日之前的请假日期
										if(@startDate<'2020-03-01')
											BEGIN
												--将请假总时间分为两部分，一部分是3月1日之前的，另一部分是3月1日之后的
												DECLARE @beforeTime int = 0;--之前总申请时间
												DECLARE @afterTime int = 0;--之和总申请时间
-- 												DECLARE @beforeLastFdTime int = 0;--之前的上年法定使用量
-- 												DECLARE @beforeLastFlTime int = 0;--之前的上年福利使用量
-- 												DECLARE @beforeFdTime int = 0;--之前的本年法定使用量
-- 												DECLARE @beforeFlTime int = 0;--之前的本年福利使用量
-- 												DECLARE @afterFdTime int = 0;--之后的本年法定使用量
-- 												DECLARE @afterFlTime int = 0;--之后的本年福利使用量

												select @beforeTime = sum(leave_minute) from bd_temp_dayDetail where order_id = @orderId and leave_date<'2020-03-01';
												set @afterTime = @totalDuration - @beforeTime;
												--将@beforeTime 按使用顺序分配给各个账户（扣除顺序：上年法定年假-->上年福利年假-->本年法定年假-->本年福利年假）
												set @temp_time = @beforeTime
												if @lastnj < @temp_time
												BEGIN
													set @temp_time = @temp_time - @lastnj
													set @lastnj = 0
													if @lastfl< @temp_time
													BEGIN
														set @temp_time = @temp_time - @lastfl
														set @lastfl = 0
														if @nj<@temp_time
														BEGIN
																set @temp_time = @temp_time - @nj
																set @nj = 0
																set @fl = @fl - @temp_time
														END
														ELSE BEGIN
															set @nj = @nj - @temp_time
														END
													END
													ELSE BEGIN
														set @lastfl = @lastfl - @temp_time
													END
												END
												ELSE BEGIN
													set @lastnj = @lastnj - @temp_time
												END
												--将@afterTime 按使用顺序分配给各个账户（扣除顺序：本年法定年假-->本年福利年假）
												set @temp_time = @beforeTime
												if @nj<@temp_time
												BEGIN
														set @temp_time = @temp_time - @nj
														set @nj = 0
														set @fl = @fl - @temp_time
												END
												ELSE BEGIN
													set @nj = @nj - @temp_time
												END
											END
										-- 不包含3月1日之前的请假日期，则全部使用当年年假
										ELSE BEGIN
											set @temp_time = @totalDuration
												if @nj<@temp_time
												BEGIN
														set @temp_time = @temp_time - @nj
														set @nj = 0
														set @fl = @fl - @temp_time
												END
												ELSE BEGIN
													set @nj = @nj - @temp_time
												END
										END
									END
								--3月1日之后（包含）申请，即申请日期为3月1日之后的，只能使用当年的年假
								ELSE BEGIN
									set @temp_time = @totalDuration
												if @nj<@temp_time
												BEGIN
														set @temp_time = @temp_time - @nj
														set @nj = 0
														set @fl = @fl - @temp_time
												END
												ELSE BEGIN
													set @nj = @nj - @temp_time
												END
								END
								--逻辑处理之后实际可用年假总量（对应该单子）
								set @usable_time = @totalDuration+@fl;
							END

							if(@totalDuration>@usable_time)
								BEGIN
									--积累
									set @totalCount = @totalCount+1;
									--判断临时表#return_table中是否有这个人的信息，有则修改，无则添加
									select @count = count(1) from #return_table where user_serial = @user_serial;
									if(@count>0)
										BEGIN
											update #return_table set error_msg = '1.'+error_msg+'，2.假类可用额度不足' where user_serial = @user_serial;
										END
										ELSE BEGIN
											insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
											SELECT
												u.user_serial,
												u.user_no,
												u.user_lname,
												d.dep_name,
												'假类可用额度不足'
											FROM
												dt_user u
												INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
												where u.user_serial = @user_serial
										END
								END
						END

					END

					--合格的人则放入#qualified_people
					if @totalCount = 0
						BEGIN

							insert into #qualified_people
							select  * from bd_temp_checkPeople where user_serial = @user_serial and order_id = @orderId;
							--bd_nianjia冻结,1冻结调休，2冻结年假
							if @ishaveedu=1
							BEGIN
								--冻结调休
								update bd_nianjia
									set txts = txts - @totalDuration,
											sdtx = isnull(sdtx,0) + @totalDuration
									where user_serial = @user_serial

								--插入bd_kq_holidays 作为扣时账户信息ho_type 2:调休
								set @usable_time = @usable_time - @totalDuration;
								insert into bd_kq_holidays (
									user_serial,
									dept_serial,
									dept_name,
									ho_year,
									ho_month,
									ho_date,
									ho_type,
									last_time,
									use_time,
									remain_time,
									related_id,
									related_result,
									create_by,
									create_time,
									vacation_total
									)
									VALUES
									(@user_serial,@dept_serial,@dept_name,@ho_year,@ho_month,GETDATE(),2,@usable_time,-@totalDuration,@usable_time,@orderId,0,@glyNo,GETDATE(),@txcount)
							END
							ELSE IF @ishaveedu=2
							BEGIN

							--
								--冻结年假
								--扣除顺序：上年法定年假-->上年福利年假-->本年法定年假-->本年福利年假
-- 								set @temp_time = @totalDuration
-- 								if @lastnj < @temp_time
-- 								BEGIN
-- 									set @temp_time = @temp_time - @lastnj
-- 									set @lastnj = 0
-- 									if @lastfl< @temp_time
-- 									BEGIN
-- 										set @temp_time = @temp_time - @lastfl
-- 										set @lastfl = 0
-- 										if @nj<@temp_time
-- 										BEGIN
-- 												set @temp_time = @temp_time - @nj
-- 												set @nj = 0
-- 												set @fl = @fl - @temp_time
-- 										END
-- 										ELSE BEGIN
-- 											set @nj = @nj - @temp_time
-- 										END
-- 									END
-- 									ELSE BEGIN
-- 										set @lastfl = @lastfl - @temp_time
-- 									END
-- 								END
-- 								ELSE BEGIN
-- 									set @lastnj = @lastnj - @temp_time
-- 								END
								update bd_nianjia
									set fdnjts = @nj,
											flnjts = @fl,
											lastfdnj = @lastnj,
											lastflnj = @lastfl,
											sdnj = isnull(sdnj,0) + @totalDuration
									where user_serial = @user_serial

								--插入bd_kq_holidays 作为扣时账户信息ho_type ,3:上年法定年假，4：上年福利年假；5：本年法定年假，6：本年福利年假
								insert into bd_kq_holidays (
									user_serial,
									dept_serial,
									dept_name,
									ho_year,
									ho_month,
									ho_date,
									ho_type,
									last_time,
									last_time_welfare,
									last_year_time,
									last_year_time_welfare,
									use_time,
									remain_time,
									related_id,
									related_result,
									create_by,
									create_time,
									vacation_total
									)
									VALUES
									(@user_serial,@dept_serial,@dept_name,@ho_year,@ho_month,GETDATE(),3,@nj,@fl,@lastnj,@lastfl,@old_lastnj-@lastnj,@nj+@fl+@lastnj+@lastfl,@orderId,0,@glyNo,GETDATE(),@njcount),
									(@user_serial,@dept_serial,@dept_name,@ho_year,@ho_month,GETDATE(),4,@nj,@fl,@lastnj,@lastfl,@old_lastfl-@lastfl,@nj+@fl+@lastnj+@lastfl,@orderId,0,@glyNo,GETDATE(),@njcount),
									(@user_serial,@dept_serial,@dept_name,@ho_year,@ho_month,GETDATE(),5,@nj,@fl,@lastnj,@lastfl,@old_nj-@nj,@nj+@fl+@lastnj+@lastfl,@orderId,0,@glyNo,GETDATE(),@njcount),
									(@user_serial,@dept_serial,@dept_name,@ho_year,@ho_month,GETDATE(),6,@nj,@fl,@lastnj,@lastfl,@old_fl-@fl,@nj+@fl+@lastnj+@lastfl,@orderId,0,@glyNo,GETDATE(),@njcount),
									(@user_serial,@dept_serial,@dept_name,@ho_year,@ho_month,GETDATE(),1,@nj,@fl,@lastnj,@lastfl,-@totalDuration,@nj+@fl+@lastnj+@lastfl,@orderId,0,@glyNo,GETDATE(),@njcount)
							END
						END
					FETCH NEXT FROM userOfLeave INTO @user_serial,@dept_serial,@dept_name
				END

			--根据#qualified_people中是否有数据，来判断是否有人不满足请假条件，有满足，则保存请假单和请假明细
			select @count = count(1) from #qualified_people;

			if(@count>0)
				BEGIN
				--根据ishaveedu来判断status的值是0（待审批）还是4（自动审批）
				set @status = 4;
				if @ishaveedu!=0
				begin
					set @status = 0;
				end
				--保存请假单
				insert into bd_leave_order(
					order_id,
					order_no,
					leave_id,
					start_date,
					end_date,
					creater_id,
					people_num,
					total_time,
					remark,
					apply_type,
					status
				)VALUES(
					@orderId,
					@orderNo,
					@leaveId,
					@startDate,
					@endDate,
					@glyNo,
					@count,
					@totalDuration*@count,
					@description,
					@applyType,
					@status
				)
				--保存请假明细
					insert into lr_kqingj(user_serial,bh,kssj,jssj,jsjg,gly_no,order_id)
					SELECT
						p.user_serial,
						d.leave_id,
						d.leave_date,
						d.leave_date,
						d.leave_minute,
						@glyNo,
						d.order_id
					FROM
						bd_temp_dayDetail d,
						#qualified_people p
					WHERE
						d.order_id = @orderId
				END

--SELECT * FROM bd_temp_dayDetail
--SELECT * FROM bd_temp_checkPeople
			--返回的数据
			SELECT
						user_no AS 'userNo',
						user_name AS 'userName',
						dept_name AS 'deptName',
						error_msg as 'errorMsg'
						from #return_table;

      --计算考勤
      DECLARE @users VARCHAR(MAX) = ''

      SELECT
        @users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
      FROM #qualified_people u
      WHERE u.order_id = @orderId

			SET @users = SUBSTRING(@users,0, LEN(@users))
      EXEC bd_proc_timecard_extend @startDate, @endDate, @users,2

			delete from bd_temp_dayDetail where order_id = @orderId;
			delete from bd_temp_checkPeople where order_id = @orderId;



			DROP TABLE #return_table;
			DROP TABLE #qualified_people;
			commit tran
		end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
				CLOSE userOfLeave --关闭游标
				DEALLOCATE userOfLeave --释放游标
			END;
	end catch

END